/**
 * 数据库初始化脚本
 * Database Initialization Script
 */

const mysql = require('mysql2/promise');
const bcrypt = require('bcryptjs');
const fs = require('fs').promises;
const path = require('path');

// 动态加载配置
let config;
try {
  config = require('../server/config/config');
} catch (error) {
  // 如果配置文件不存在，使用环境变量
  const envPath = path.join(__dirname, '../.env');
  console.log('Loading .env from:', envPath);
  const result = require('dotenv').config({ path: envPath });
  if (result.error) {
    console.error('Error loading .env file:', result.error);
  } else {
    console.log('Loaded', Object.keys(result.parsed || {}).length, 'environment variables');
  }
  config = {
    database: {
      host: process.env.DB_HOST || 'localhost',
      port: process.env.DB_PORT || 3306,
      user: process.env.DB_USER || 'notes_user',
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME || 'personal_cloud_notes'
    },
    security: {
      bcryptRounds: parseInt(process.env.BCRYPT_ROUNDS) || 10
    },
    storage: {
      defaultLimit: parseInt(process.env.DEFAULT_STORAGE_LIMIT) || 32212254720
    },
    upload: {
      path: process.env.UPLOAD_PATH || '/var/www/personal-cloud-notes/data/users',
      tempPath: process.env.UPLOAD_PATH ? path.join(process.env.UPLOAD_PATH, 'temp') : '/var/www/personal-cloud-notes/data/temp'
    },
    backup: {
      path: process.env.BACKUP_PATH || '/var/www/personal-cloud-notes/data/backups'
    },
    logging: {
      path: process.env.LOG_PATH || '/var/www/personal-cloud-notes/data/logs'
    }
  };
}

async function initDatabase() {
  let connection;

  try {
    console.log('开始初始化数据库... / Starting database initialization...');

    // 连接到MySQL服务器（不指定数据库）
    connection = await mysql.createConnection({
      host: config.database.host,
      port: config.database.port,
      user: config.database.user,
      password: config.database.password,
      multipleStatements: true
    });

    console.log('数据库连接成功 / Database connection successful');

    // 读取并执行SQL schema文件
    const schemaPath = path.join(__dirname, '../database/schema.sql');

    try {
      const schemaSql = await fs.readFile(schemaPath, 'utf8');
      console.log('正在执行数据库表结构创建... / Executing database schema creation...');

      // 执行SQL脚本
      await connection.execute(schemaSql);
      console.log('数据库表结构创建成功 / Database schema created successfully');

    } catch (fileError) {
      console.warn('无法读取schema.sql文件，使用内置表结构 / Cannot read schema.sql, using built-in schema');
      connection = await createTablesManually(connection);
    }

    // 重新连接到目标数据库
    await connection.end();
    connection = await mysql.createConnection({
      host: config.database.host,
      port: config.database.port,
      user: config.database.user,
      password: config.database.password,
      database: config.database.database,
      multipleStatements: true
    });

    // 检查是否已存在管理员用户
    try {
      const [adminUsers] = await connection.execute(
        'SELECT COUNT(*) as count FROM users WHERE role = ?',
        ['admin']
      );

      if (adminUsers[0].count === 0) {
        // 创建默认管理员用户
        const adminEmail = 'admin@example.com';
        const adminPassword = 'admin123456';
        const hashedPassword = await bcrypt.hash(adminPassword, config.security.bcryptRounds);

        await connection.execute(`
          INSERT INTO users (email, password, nickname, role, storage_limit) 
          VALUES (?, ?, ?, ?, ?)
        `, [adminEmail, hashedPassword, '系统管理员', 'admin', config.storage.defaultLimit]);

        console.log('默认管理员用户创建成功 / Default admin user created successfully');
        console.log(`管理员邮箱 / Admin email: ${adminEmail}`);
        console.log(`管理员密码 / Admin password: ${adminPassword}`);
        console.log('请在生产环境中及时修改默认密码！/ Please change the default password in production!');
      } else {
        console.log('管理员用户已存在，跳过创建 / Admin user already exists, skipping creation');
      }
    } catch (userError) {
      console.warn('创建管理员用户时出错，可能表不存在，尝试手动创建 / Error creating admin user, tables may not exist');
      
      // 尝试手动创建管理员用户
      const adminEmail = 'admin@example.com';
      const adminPassword = 'admin123456';
      const hashedPassword = await bcrypt.hash(adminPassword, config.security.bcryptRounds);

      try {
        await connection.execute(`
          INSERT IGNORE INTO users (email, password, nickname, role, storage_limit) 
          VALUES (?, ?, ?, ?, ?)
        `, [adminEmail, hashedPassword, '系统管理员', 'admin', config.storage.defaultLimit]);
        
        console.log('默认管理员用户创建成功 / Default admin user created successfully');
        console.log(`管理员邮箱 / Admin email: ${adminEmail}`);
        console.log(`管理员密码 / Admin password: ${adminPassword}`);
      } catch (insertError) {
        console.error('创建管理员用户失败 / Failed to create admin user:', insertError.message);
      }
    }

    // 创建必要的目录结构
    await createDirectoryStructure();

    // 验证数据库结构
    await validateDatabaseStructure(connection);

    console.log('数据库初始化完成！/ Database initialization completed!');

  } catch (error) {
    console.error('数据库初始化失败 / Database initialization failed:', error);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

/**
 * 手动创建表结构（备用方案）
 */
async function createTablesManually(connection) {
  console.log('使用内置表结构创建数据库... / Creating database with built-in schema...');

  // 创建数据库
  await connection.execute(`CREATE DATABASE IF NOT EXISTS \`${config.database.database}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);

  // 关闭当前连接并重新连接到指定数据库
  await connection.end();
  connection = await mysql.createConnection({
    host: config.database.host,
    port: config.database.port,
    user: config.database.user,
    password: config.database.password,
    database: config.database.database,
    multipleStatements: true
  });

  // 创建用户表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      email VARCHAR(100) UNIQUE NOT NULL,
      password VARCHAR(255) NOT NULL,
      nickname VARCHAR(50) NOT NULL,
      avatar VARCHAR(255) DEFAULT NULL,
      role ENUM('user', 'admin') DEFAULT 'user',
      storage_used BIGINT DEFAULT 0,
      storage_limit BIGINT DEFAULT ${config.storage.defaultLimit},
      last_login_at TIMESTAMP NULL DEFAULT NULL,
      last_login_ip VARCHAR(45) DEFAULT NULL,
      is_active TINYINT(1) DEFAULT 1,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      INDEX idx_email (email),
      INDEX idx_role (role),
      INDEX idx_is_active (is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  // 创建笔记表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS notes (
      id INT PRIMARY KEY AUTO_INCREMENT,
      user_id INT NOT NULL,
      title VARCHAR(200) NOT NULL,
      content LONGTEXT,
      content_type ENUM('html', 'markdown', 'plain') DEFAULT 'html',
      tags VARCHAR(500) DEFAULT NULL,
      is_favorite TINYINT(1) DEFAULT 0,
      is_deleted TINYINT(1) DEFAULT 0,
      deleted_at TIMESTAMP NULL DEFAULT NULL,
      view_count INT DEFAULT 0,
      word_count INT DEFAULT 0,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      INDEX idx_user_id (user_id),
      INDEX idx_updated_at (updated_at),
      INDEX idx_is_favorite (is_favorite),
      INDEX idx_is_deleted (is_deleted),
      FULLTEXT idx_content (title, content)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  // 创建文件表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS files (
      id INT PRIMARY KEY AUTO_INCREMENT,
      user_id INT NOT NULL,
      filename VARCHAR(255) NOT NULL,
      original_name VARCHAR(255) NOT NULL,
      file_path VARCHAR(500) NOT NULL,
      file_size BIGINT NOT NULL,
      mime_type VARCHAR(100) NOT NULL,
      file_type ENUM('image', 'video', 'audio', 'document', 'other') DEFAULT 'other',
      folder_path VARCHAR(500) DEFAULT '/',
      is_public TINYINT(1) DEFAULT 0,
      download_count INT DEFAULT 0,
      thumbnail_path VARCHAR(500) DEFAULT NULL,
      is_deleted TINYINT(1) DEFAULT 0,
      deleted_at TIMESTAMP NULL DEFAULT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      INDEX idx_user_id (user_id),
      INDEX idx_filename (filename),
      INDEX idx_file_type (file_type),
      INDEX idx_folder_path (folder_path),
      INDEX idx_created_at (created_at),
      INDEX idx_is_deleted (is_deleted)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  console.log('基础表结构创建成功 / Basic table structure created successfully');
  return connection;
}

/**
 * 创建必要的目录结构
 */
async function createDirectoryStructure() {
  const directories = [
    config.upload.path,
    config.upload.tempPath,
    config.backup.path,
    config.logging.path,
    path.join(config.upload.path, 'avatars'),
    path.join(config.upload.path, 'temp')
  ];

  for (const dir of directories) {
    try {
      await fs.mkdir(dir, { recursive: true });
      console.log(`目录创建成功 / Directory created: ${dir}`);
    } catch (error) {
      if (error.code !== 'EEXIST') {
        console.warn(`目录创建失败 / Failed to create directory ${dir}:`, error.message);
      }
    }
  }
}

/**
 * 验证数据库结构
 */
async function validateDatabaseStructure(connection) {
  console.log('验证数据库结构... / Validating database structure...');

  const requiredTables = ['users', 'notes', 'files'];

  for (const table of requiredTables) {
    try {
      const [rows] = await connection.execute(`SHOW TABLES LIKE '${table}'`);
      if (rows.length === 0) {
        throw new Error(`表 ${table} 不存在 / Table ${table} does not exist`);
      }
      console.log(`✓ 表 ${table} 验证成功 / Table ${table} validated`);
    } catch (error) {
      console.error(`✗ 表 ${table} 验证失败 / Table ${table} validation failed:`, error.message);
      throw error;
    }
  }

  console.log('数据库结构验证完成 / Database structure validation completed');
}

// 如果直接运行此脚本
if (require.main === module) {
  initDatabase();
}

module.exports = initDatabase;